package dao.dao;

import dao.bean.User;
import dao.exception.NotUniqueUserEmailException;
import dao.exception.NotUniqueUserLoginException;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoJdbc implements UserDao {
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new ExceptionInInitializerError(e);
        }
    } 

    private Connection getConnection() throws SQLException {
        // error: empty url - no driver
        // error: wrong db name
//        return DriverManager.getConnection("jdbc:mysql://localhost:3306/production_eshop", "username", "password");
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/production_eshop", "root", "2479");
    }

    @Override
    public List<User> selectAll() throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("SELECT id, login, email FROM User");
            rs = ps.executeQuery();
            ArrayList<User> result = new ArrayList<User>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String login = rs.getString("login");
                String email = rs.getString("email");
                User user = new User(id);
                user.setLogin(login);
                user.setEmail(email);
                result.add(user);
            }
            conn.commit();
            return result;
        } catch (SQLException e) {
            JdbcUtils.rollbackQuietly(conn);
            throw e;
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public int deleteById(int id) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("DELETE FROM User WHERE id = ?");
            ps.setInt(1, id);
            int result = ps.executeUpdate();
            conn.commit();
            return result;
        } catch (SQLException e) {
            JdbcUtils.rollbackQuietly(conn);
            throw e;
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public void insert(User user) throws SQLException, NotUniqueUserLoginException, NotUniqueUserEmailException {
        Connection conn = getConnection();        
        PreparedStatement ps = null;
        try {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);            
            
//            if (existWithLogin0(conn, user.getLogin())) {
//                throw new NotUniqueUserLoginException("Login '" + user.getLogin() + "' doubled");
//            }
//            if (existWithEmail0(conn, user.getEmail())) {
//                throw new NotUniqueUserEmailException("Email '" + user.getEmail() + "' doubled");
//            }
            
            ps = conn.prepareStatement("INSERT INTO User (login, email) VALUES (?, ?)");
            ps.setString(1, user.getLogin());
            ps.setString(2, user.getEmail());
            ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            JdbcUtils.rollbackQuietly(conn);
            throw e;
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    private boolean existWithLogin0(Connection conn, String login) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("SELECT id FROM User WHERE login=?");
        ps.setString(1, login);
        ResultSet rs = ps.executeQuery();
        return rs.next();
    }

    private boolean existWithEmail0(Connection conn, String email) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("SELECT id FROM User WHERE email=?");
        ps.setString(1, email);
        ResultSet rs = ps.executeQuery();
        return rs.next();
    }

    @Override
    public void shutdown() {
        // NOP
    }
}

